﻿using System;
using System.Collections.Generic;
using System.Text;
//using System.Windows.Forms;

using Microsoft.Office;
using Microsoft.Office.Interop;
using Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;

using System.Security.Cryptography;
using System.Security.Permissions;
using System.IO;

using System.Text.RegularExpressions;

using System.Data;
using System.Data.OleDb;



namespace japanese
{
    class ExcelOperation
    {
        public String filename;
        public Application apps;
        public Workbook workboos;

        private int logline = -1;

        public LogExcel log = new LogExcel();

        private String fullname = "";

        private bool deleteit = false;


        bool disflag = true;
        public void mDisplose(bool disposing)
        {
            
            if (disposing && disflag == true)
            {
                if (this.workboos != null)
                {
                    this.workboos.Close();
                }
                this.apps.Quit();
            }
            if (disposing) disflag = false;
        }


        ~ExcelOperation()
        {
            if(this.deleteit==false) this.SaveLog(false);
            this.mDisplose(false);
        }

        //从文件中新建一个excel的操作
        public ExcelOperation(String name)
        {
            try
            {

                this.filename = name;
                apps = new Microsoft.Office.Interop.Excel.ApplicationClass();
                if (apps == null)
                {
                    System.Windows.Forms.MessageBox.Show("你的电脑excel运行环境有问题，请查看");

                    return;
                }


                this.fullname = System.IO.Directory.GetCurrentDirectory() + System.IO.Path.DirectorySeparatorChar + name;
                workboos = apps.Workbooks.Open(this.fullname,
                    Type.Missing,
                    Type.Missing,
                    Type.Missing,
                    Type.Missing,
                    Type.Missing,
                    Type.Missing,
                    Type.Missing,
                    Type.Missing,
                    Type.Missing,
                    Type.Missing,
                    Type.Missing,
                    Type.Missing,
                    Type.Missing,
                    Type.Missing);

                if (workboos == null)
                {
                    System.Windows.Forms.MessageBox.Show("文件没法打开");
                    return;
                }
                this.log.filename = name;

                this.log.sheets = new List<LogSheetItem>();

                int i = 0;
                for (; i < workboos.Sheets.Count; i++)
                {
                    this.log.sheets.Add(new LogSheetItem(i + 1));
                }

            }
            catch(Exception e){
                System.Windows.Forms.MessageBox.Show("读取excel文件出错 :"+e.ToString());
            }
        }



        public int SetSheetOffset(int index, int offset)
        {
            if (this.log.sheets.Count > 0) {

                foreach (LogSheetItem i in this.log.sheets)
                {
                    if (i.SheetIndex == index) { i.SheetOffset = offset; return 0; }
                }

                return -1;
            }

            return -1;
        }


        public int  LogLine{

            set {
                this.logline = value;
            }

            get {
                return this.logline;
            }

        }

        //读取上一次记录数据
        static public LinkedList<ExcelOperation> RecoverLog()
        {
            LinkedList<ExcelOperation> r = new LinkedList<ExcelOperation>();
            StreamReader rd;
            String fn = System.IO.Directory.GetCurrentDirectory() + System.IO.Path.DirectorySeparatorChar + LogFile.logfile;
            try
            {
                
                rd = new StreamReader(fn, Encoding.Default);
            }
            catch (Exception e)
            {
                System.Windows.Forms.MessageBox.Show("无法找开文件" + LogFile.logfile);
                return null;
            }

            int line = 0;
            String str;
            for (str = rd.ReadLine(); str != null; str = rd.ReadLine())
            {
                line++;
                Match m = Regex.Match(str, @"logexcel:\[([^[]*.xlsx)\](\[[0-9]*\,[0-9]*\])*");
                if (m.Groups.Count > 1)
                {
                    //添加文件
                    ExcelOperation ne;
                    try
                    {
                       ne = new ExcelOperation(m.Groups[1].Value.ToString());
                       int items = 0;
                       Group g = m.Groups[2];
                       for(items = 0; items < g.Captures.Count;items++)
                       {
                           String sheetlog = g.Captures[items].Value.ToString();
                           Match t = Regex.Match(sheetlog, @"\[(\d*),(\d*)]");

                           ne.SetSheetOffset(int.Parse(t.Groups[1].Value.ToString()), int.Parse(t.Groups[2].Value.ToString()));
                           
                       }

                       ne.LogLine = line;
                       r.AddLast(ne);
                    }
                    catch {
                        continue;
                    }

                    

                }
 
            }
            rd.Close();
            return r;
        }


        //写入一次记录
        private String toSaveLogString(){
                String ss = "";
                ss = String.Format("logexcel:[{0}]", this.filename);
                if (this.log.sheets.Count > 0)
                {
                    LogSheetItem[] it = new LogSheetItem[this.log.sheets.Count];
                    log.sheets.CopyTo(it, 0);
                    foreach (LogSheetItem aa in it)
                    {
                        ss += String.Format("[{0},{1}]", aa.SheetIndex, aa.SheetOffset);
                    }
                }
                ss += "\r\n";
                return ss;
        }
         public int SaveLog(bool Delete)
        {

            this.deleteit = Delete;
            //读 取所有的文件数据
            String all = "";
            int count = 0;
            StreamReader rd;
            Encoding targetEncoding;
            String fn = System.IO.Directory.GetCurrentDirectory() + System.IO.Path.DirectorySeparatorChar + LogFile.logfile;
            try
            {
                targetEncoding = Unility.TxtFileEncoding.GetEncoding(fn, Encoding.Default);
                rd = new StreamReader(fn, targetEncoding);
            }
            catch (Exception e)
            {
                System.Windows.Forms.MessageBox.Show("无法找开文件" + LogFile.logfile);
                return -1;
            }

            String str;
            for (str = rd.ReadLine(); str != null; str = rd.ReadLine())
            {
                count++;
                if (count != this.logline)
                {
                    all += str + "\r\n";
                }
                else
                {
                    if (Delete)
                    {
                        all += "\r\n";
                    }
                    else {
                        all += toSaveLogString();
                    }
                }
            }

            rd.Close();

            if (this.deleteit==false && this.logline == -1) {
                all += toSaveLogString();
            }


            StreamWriter wt;
            try
            {
                wt = new StreamWriter(fn, false, targetEncoding);
            }
            catch (Exception e)
            {
                System.Windows.Forms.MessageBox.Show("无法找开文件" + LogFile.logfile);
                return -1;
            }

            wt.WriteLine(all);

            wt.Close();

            return 0;

        }

        public System.Windows.Forms.TreeNode ToTreeNode()
        {
            if (this.apps == null || this.workboos == null || this.workboos.Worksheets.Count < 1) return null;
            //加入到
            System.Windows.Forms.TreeNode ntn = new System.Windows.Forms.TreeNode(this.filename);
            foreach (Worksheet sheetname in this.workboos.Worksheets)
            {
                ntn.Nodes.Add(sheetname.Name);
            }

            return ntn;
        }


        public LogSheetItem LogSheetItemGet(int index)
        {
            if(this.log.sheets.Count < index) return null;
            foreach (LogSheetItem i in this.log.sheets)
            {
                if (i.SheetIndex == index) return i;
            }

            return null;
        }

        public int LogSheetItemSet(int index, int crow)
        {
            if (this.log.sheets.Count < index) return -1;
            foreach (LogSheetItem i in this.log.sheets)
            {
                if (i.SheetIndex == index) { i.SheetOffset = crow; return 0; }
            }

            return -1;
        }

        //读写文件
        public String ReadSheet(int index, int row, int col)
        {
            if (this.workboos.Worksheets.Count < index) return "";
            else {

                Worksheet sheet = (Worksheet)this.workboos.Worksheets[index];
                if (sheet.Cells.Rows.Count < row
                    || sheet.Cells.Columns.Count < col) return "";
                else {
                    try
                    {
                        return ((this.workboos.Worksheets[index] as Worksheet).Cells[row, col] as Range).Text.ToString();
                    }
                    catch { return "数据不存在"; }
                }
            }
        }



        /// <summary>
        /// 得某文件夹下所有的文件
        /// </summary>
        /// <param name="directory">文件夹名称</param>
        /// <param name="pattern">搜寻指类型</param>
        /// <returns></returns>
        public  string GetFiles(DirectoryInfo directory, string pattern)
        {
            string result = "";

            if (directory.Exists || pattern.Trim() != string.Empty)
            {

              

                foreach (FileInfo info in directory.GetFiles(pattern))
                {
                    result = result + info.FullName.ToString() + ";";
                    //result = result + info.Name.ToString() + ";";
                }

                foreach (DirectoryInfo info in directory.GetDirectories())
                {
                    GetFiles(info, pattern);
                }
            }

            return result;

        }



        public System.Data.DataTable ReadExcel(string filePath,int sindex)
        {
            try
            {
                string strConn;
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
                OleDbConnection OleConn = new OleDbConnection(strConn);
                OleConn.Open();
                String n = (this.workboos.Worksheets[sindex] as Worksheet).Name;
                String sql = String.Format("SELECT * FROM  [{0}]",n);//可是更改Sheet名称，比如sheet2，等等

                OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
                DataSet OleDsExcle = new DataSet();
                OleDaExcel.Fill(OleDsExcle,n);
                OleConn.Close();

                return OleDsExcle.Tables[n];
            }
            catch (Exception err)
            {
               System.Windows.Forms.MessageBox.Show("数据绑定Excel失败!失败原因：" + err.Message, "提示信息",
                   System.Windows.Forms.MessageBoxButtons.OK, 
                   System.Windows.Forms.MessageBoxIcon.Information);
                return null;
            }
        }


    }




}
